#! /bin/bash
export LANG=zh_CN.UTF-8
PRESTO_HOME=/export/server/presto/bin/presto


${PRESTO_HOME} --catalog hive --server 192.168.88.80:8090 --execute "--客户意向宽表
INSERT INTO $1zx_dm.dm_relationship
WITH tmp AS (SELECT SUBSTRING(date_time, 1, 4)  AS year_code,
                    SUBSTRING(date_time, 6, 2)  AS month_code,
                    SUBSTRING(date_time, 1, 7)  AS year_month,
                    SUBSTRING(date_time, 9, 2)  AS day_month_num,
                    SUBSTRING(date_time, 1, 10) AS dim_date,
                    SUBSTRING(date_time, 12, 2) AS hour_code,
                    date_time                   AS full_time,
                    online_offline,
                    new_or_old,
                    origin_type,
                    stu_area,
                    itcast_subject_id,
                    itcast_subject_name,
                    itcast_school_id,
                    itcast_school_name,
                    consult_centre_id,
                    consult_centre,
                    group_type,
                    intended_count
             FROM $1zx_dws.dws_relationship_hourcount)

--              SELECT sum(intended_count)
--              FROM tmp
--              WHERE dim_date='2011-08-24' and online_offline ='offline' AND new_or_old='old' AND consult_centre_id='2'

   , tmp2 AS (SELECT '2022-05-17'         AS date_time,
                     CASE GROUPING(year_code, month_code, day_month_num, hour_code)
                         WHEN 7 THEN 'year'
                         WHEN 3 THEN 'month'
                         WHEN 1 THEN 'day'
                         WHEN 0 THEN 'hour'
                         ELSE 'other' END AS
                                             time_type,
                     year_code,
                     month_code,
                     year_month,
                     day_month_num,
                     dim_date,
                     hour_code,
                     full_time,

                     online_offline,
                     new_or_old,
                     origin_type,
                     stu_area,
                     itcast_subject_id,
                     itcast_subject_name,
                     itcast_school_id,
                     itcast_school_name,
                     consult_centre_id,
                     consult_centre,
                     group_type           AS group_type_old,
                     CASE GROUPING(new_or_old,
                                   stu_area,
                                   itcast_subject_id,
                                   itcast_school_id,
                                   origin_type,
                                   consult_centre_id)
                         WHEN 31 THEN 'all'
                         WHEN 15 THEN 'area'
                         WHEN 23 THEN 'subject'
                         WHEN 27 THEN 'school'
                         WHEN 29 THEN 'origin'
                         WHEN 30 THEN 'centre'
                         ELSE 'other' END AS group_type,
                     SUM(intended_count)  AS intended_count
              FROM tmp
              GROUP BY
                  GROUPING SETS (
                  --年
                  (year_code, group_type, online_offline, new_or_old),
                  (year_code, group_type, online_offline, new_or_old, stu_area),
                  (year_code, group_type, online_offline, new_or_old, itcast_subject_id, itcast_subject_name),
                  (year_code, group_type, online_offline, new_or_old, itcast_school_id, itcast_school_name),
                  (year_code, group_type, online_offline, new_or_old, origin_type),
                  (year_code, group_type, online_offline, new_or_old, consult_centre_id, consult_centre),
                  --月
                  (year_code, month_code, year_month, group_type, online_offline, new_or_old),
                  (year_code, month_code, year_month, group_type, online_offline, new_or_old, stu_area),
                  (year_code, month_code, year_month, group_type, online_offline, new_or_old, itcast_subject_id,
                   itcast_subject_name),
                  (year_code, month_code, year_month, group_type, online_offline, new_or_old, itcast_school_id,
                   itcast_school_name),
                  (year_code, month_code, year_month, group_type, online_offline, new_or_old, origin_type),
                  (year_code, month_code, year_month, group_type, online_offline, new_or_old, consult_centre_id,
                   consult_centre),
                  --日
                  (year_code, month_code, year_month, day_month_num, dim_date, group_type, online_offline, new_or_old),
                  (year_code, month_code, year_month, day_month_num, dim_date, group_type, online_offline, new_or_old,
                   stu_area),
                  (year_code, month_code, year_month, day_month_num, dim_date, group_type, online_offline, new_or_old,
                   itcast_subject_id, itcast_subject_name),
                  (year_code, month_code, year_month, day_month_num, dim_date, group_type, online_offline, new_or_old,
                   itcast_school_id, itcast_school_name),
                  (year_code, month_code, year_month, day_month_num, dim_date, group_type, online_offline, new_or_old,
                   origin_type),
                  (year_code, month_code, year_month, day_month_num, dim_date, group_type, online_offline, new_or_old,
                   consult_centre_id, consult_centre),
                  --时
                  (year_code, month_code, year_month, day_month_num, dim_date, hour_code, full_time, group_type,
                   online_offline, new_or_old),
                  (year_code, month_code, year_month, day_month_num, dim_date, hour_code, full_time, group_type,
                   online_offline, new_or_old, stu_area),
                  (year_code, month_code, year_month, day_month_num, dim_date, hour_code, full_time, group_type,
                   online_offline, new_or_old, itcast_subject_id, itcast_subject_name),
                  (year_code, month_code, year_month, day_month_num, dim_date, hour_code, full_time, group_type,
                   online_offline, new_or_old, itcast_school_id, itcast_school_name),
                  (year_code, month_code, year_month, day_month_num, dim_date, hour_code, full_time, group_type,
                   online_offline, new_or_old, origin_type),
                  (year_code, month_code, year_month, day_month_num, dim_date, hour_code, full_time, group_type,
                   online_offline, new_or_old, consult_centre_id, consult_centre)
                  ))
SELECT date_time,
       time_type,
       year_code,
       month_code,
       year_month,
       day_month_num,
       dim_date,
       hour_code,
       full_time,
       online_offline,
       new_or_old,
       origin_type,
       stu_area,
       itcast_subject_id,
       itcast_subject_name,
       itcast_school_id,
       itcast_school_name,
       consult_centre_id,
       consult_centre,
       group_type,
       CAST(intended_count AS INT) AS intended_count
FROM tmp2
WHERE group_type_old = group_type;

--客户线索宽表
INSERT INTO $1zx_dm.dm_clue
WITH tmp AS (SELECT SUBSTRING(date_time, 1, 4)  AS year_code,
                    SUBSTRING(date_time, 6, 2)  AS month_code,
                    SUBSTRING(date_time, 1, 7)  AS year_month,
                    SUBSTRING(date_time, 9, 2)  AS day_month_num,
                    SUBSTRING(date_time, 1, 10) AS dim_date,
                    SUBSTRING(date_time, 12, 2) AS hour_code,
                    date_time                   AS full_time,
                    online_offline,
                    new_or_old,
                    clue_count,
                    valid_clue_count,
                    percent_conversion
             FROM $1zx_dws.dws_clue_hourcount)
SELECT '2022-05-17'                                                                    AS date_time,
       CASE GROUPING(year_code, month_code, day_month_num, hour_code)
           WHEN 7 THEN 'year'
           WHEN 3 THEN 'month'
           WHEN 1 THEN 'day'
           WHEN 0 THEN 'hour'
           ELSE 'other' END
                                                                                       AS time_type,
       year_code,
       month_code,
       year_month,
       day_month_num,
       dim_date,
       hour_code,
       full_time,
       online_offline,
       new_or_old,
       CAST(SUM(clue_count) AS INT)                                                    AS clue_count,
       CAST(SUM(valid_clue_count) AS INT)                                              AS valid_clue_count,
       CAST(SUM(valid_clue_count) * 1.000000 / SUM(clue_count) * 100 AS DECIMAL(6, 3)) AS percent_conversion
FROM tmp
GROUP BY
    GROUPING SETS ((year_code, online_offline, new_or_old),
                   (year_code, month_code, year_month, online_offline, new_or_old),
                   (year_code, month_code, year_month, day_month_num, dim_date, online_offline, new_or_old),
                   (year_code, month_code, year_month, day_month_num, dim_date, hour_code, full_time, online_offline,
                    new_or_old)
    );

"